﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace PagedDataGridView
{
    public class AdoNetPagedQueryExecutor : IPagedQueryExecutor
    {
        private IPageData _pageData;
        private string _sqlQueryString;
        private string _filterString;
        #region IPagedQueryExecutor Members

        public DataTable ReadNextPage()
        {
            if (_pageData.Page+1 < _pageData.Pages)
                _pageData.Page++;
            return ExecuteAdoQuery();
        }

        public DataTable ReadPrevPage()
        {
            if (_pageData.Page > 0)
                _pageData.Page--;
            return ExecuteAdoQuery();
        }

        public DataTable ReadFirstPage()
        {
            _pageData.Page = 0;
            return ExecuteAdoQuery();
        }

        public DataTable ReadLastPage()
        {
            _pageData.Page = _pageData.Pages - 1;
            return ExecuteAdoQuery();
        }

        public DataTable ChangePageSize(int newSize)
        {
            _pageData.PageSize = newSize;
            _pageData.Page = 0;
            return ExecuteAdoQuery();
        }

        public void SetQuery(string sqlQuery)
        {
            _sqlQueryString = sqlQuery;
        }

        public void SetPage(IPageData pageData)
        {
            _pageData = pageData;
        }

        public DataTable ReadCurrentPage()
        {
            return ExecuteAdoQuery();
        }

        public string BuildTotalString()
        {
            return string.Format("{0}-{1} из {2}", _pageData.StartRow,
                                 Math.Min(_pageData.EndRow, _pageData.TotalRows), _pageData.TotalRows);
        }

        public DataTable ApplyFilters(List<Filter> filters)
        {
            _filterString = "";
            foreach (var filter in filters)
            {
                if (!string.IsNullOrWhiteSpace(filter.FilterValue))
                    _filterString = string.IsNullOrWhiteSpace(_filterString)
                                        ? string.Format("{0} LIKE '{1}%'", filter.ColumnName, filter.FilterValue)
                                        : _filterString +
                                          string.Format("AND {0} LIKE '{1}%'", filter.ColumnName, filter.FilterValue);
            }

            return ExecuteAdoQuery();
        }

        #endregion

        private DataTable ExecuteAdoQuery()
        {
            var queryStringWithFilter = _sqlQueryString;
            if (!string.IsNullOrWhiteSpace(_filterString))
            {
                queryStringWithFilter = queryStringWithFilter.IndexOf("where") > 0
                                       ? string.Format("{0} AND {1}", queryStringWithFilter, _filterString)
                                       : string.Format("{0} WHERE {1}", queryStringWithFilter, _filterString);
            }
            string buildQueryString = PageDataInserter.InsertPageData(queryStringWithFilter, _pageData);
            string totalQueryString = PageDataInserter.BuildTotalsQuery(queryStringWithFilter);

            using (var connection =
                new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(totalQueryString, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        reader.Read();
                        _pageData.TotalRows = Int32.Parse(reader[0].ToString());
                    }
                }
                using (var command = new SqlCommand(buildQueryString, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        using (var dt = new DataTable())
                        {
                            dt.Load(reader);
                            return dt;
                        }
                    }
                }
            }
        }
    }

    internal class PageDataInserter
    {
        internal static string InsertPageData(string sqlQueryString, IPageData pageData)
        {
            sqlQueryString = sqlQueryString.ToLower();
            sqlQueryString = PrepareQuery(sqlQueryString, pageData);
            sqlQueryString = WrapUpSubquery(sqlQueryString, pageData.StartRow, pageData.EndRow);
            return sqlQueryString;
        }

        private static string WrapUpSubquery(string sqlQueryString, int startRow, int endRow)
        {
            return
                string.Format(
                    "SELECT * FROM ({0}) AS RowConstrainedResult WHERE RowNum >= {1} AND RowNum <= {2} ORDER BY ROWNum",
                    sqlQueryString, startRow, endRow);
        }

        private static string PrepareQuery(string sqlQueryString, IPageData pageData)
        {
            int insertIndex = sqlQueryString.IndexOf("from") - 1; //insert row counter after main projection
            if (insertIndex < 0)
                throw new ArgumentException("The query string has no select ... from statement");
            return sqlQueryString.Substring(0, insertIndex) +
                   string.Format(" , ROW_NUMBER() OVER ( ORDER BY {0} ) AS RowNum", pageData.SortBy)
                   + sqlQueryString.Substring(insertIndex);
        }

        internal static string BuildTotalsQuery(string sqlQueryString)
        {
            sqlQueryString = sqlQueryString.ToLower();
            int insertIndex = sqlQueryString.IndexOf("from") - 1; //insert row counter after main projection
            if (insertIndex < 0)
                throw new ArgumentException("The query string has no select ... from statement");
            return "Select count(*) " + sqlQueryString.Substring(insertIndex);
        }
    }
}